﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Collections.Generic;

namespace BangkokSteel
{
    public partial class frmProduct : Form, MainFormListener
    {

        private TabControl mainTab;
        private MySqlConnection conn = null;
        private MySqlDataAdapter adapter = null;
        private MySqlDataAdapter adapter2 = null;
        private Hashtable ht = new Hashtable();
        private string MODE = "";

        public frmProduct(TabControl mainTab)
        {
            this.mainTab = mainTab;
            InitializeComponent();
        }

        #region Form Event

        private void frmProduct_Load(object sender, EventArgs e)
        {
            conn = new MySqlConnection(BangkokSteel.Properties.Settings.Default.bangkoksteelConnectionString);

            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

            conn.Open();

            string strSQL = " SELECT p.id,p.name,sub.name as subcategory_name FROM product p ";
            strSQL += " LEFT JOIN product_subcategory sub on sub.id = p.product_subcategory_id; ";

            adapter = new MySqlDataAdapter(strSQL, conn);
            adapter.Fill(dataSet1, "product");
            conn.Close();
            dataGridView1.DataSource = dataSet1.Tables["product"];

            //SET Combobox
            SetContrl();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.mainTab.TabPages.Remove(this.mainTab.SelectedTab);
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            EnableOrDisableMainPage(false);

            MODE = "ADD";

            ClearFields();
            SetCheckListBox();

            panel1.Visible = true;

            txtId.Focus();
            txtId.Select();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count > 0 && dataGridView1.SelectedRows.Count > 0)
            {
                if (DialogResult.Yes == MessageBox.Show("คุณต้องการลบข้อมูลนี้ใช่หรือไม่", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                {
                    DataGridViewRow row = dataGridView1.SelectedRows[0];

                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Open();

                    MySqlTransaction trans = conn.BeginTransaction();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = trans;
                    try
                    {
                        //delete product
                        string sql = "DELETE FROM `product` WHERE (`id` = @Original_id);";
                        cmd.CommandText = sql;
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@Original_id", MySqlDbType.VarChar).Value = row.Cells["id"].Value;

                        cmd.ExecuteNonQuery();

                        //delete unit_product
                        sql = @"DELETE FROM `product_unit` WHERE (`product_id` = @Original_id);";
                        cmd.CommandText = sql;
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@Original_id", MySqlDbType.VarChar).Value = row.Cells["id"].Value;
                        cmd.ExecuteNonQuery();

                        trans.Commit();
                        conn.Close();

                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        conn.Close();
                        MessageBox.Show(ex.ToString());
                        return;
                    }
                    MessageBox.Show("ลบข้อมูลเรียบร้อยแล้ว");
                    MODE = "";

                    // Refresh for New Data
                    dataSet1.Tables["product"].Clear();
                    adapter.Fill(dataSet1, "product");  
                }
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count > 0 && dataGridView1.SelectedRows.Count > 0)
            {
                EnableOrDisableMainPage(false);

                DataGridViewRow row = dataGridView1.SelectedRows[0];
                MODE = "EDIT";

                txtId.Text = row.Cells["id"].Value.ToString();
                txtName.Text = row.Cells["name"].Value.ToString();
                cboProductSubcategory.SelectedText = row.Cells["subcategory_name"].Value.ToString();

                SetCheckListBox(row.Cells["id"].Value.ToString());
                txtId.Enabled = false;
                panel1.Visible = true;

                txtName.Focus();
                txtName.SelectAll();
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            EnableOrDisableMainPage(true);

            panel1.Visible = false;
            MODE = "";
            ClearFields();
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            try
            {
                if (Validation())
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Open();

                    MySqlTransaction trans = conn.BeginTransaction();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = trans;

                    if ("ADD".Equals(MODE))
                    {
                        try
                        {
                            //insert product
                            string sql = @"insert into product(`id`,`name`,`product_subcategory_id`) values(@id,@name,@product_subcategory_id)";
                            cmd.CommandText = sql;
                            cmd.CommandType = CommandType.Text;
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@id", MySqlDbType.VarChar).Value = txtId.Text;
                            cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = txtName.Text;
                            cmd.Parameters.Add("@product_subcategory_id", MySqlDbType.Int32).Value = cboProductSubcategory.SelectedValue;

                            int affectedRow = cmd.ExecuteNonQuery();


                            //insert unit
                            foreach (string i in chkListBox1.CheckedItems)
                            {
                                if (ht.ContainsKey(i))
                                {
                                    sql = @"insert into product_unit(`unit_id`,`product_id`) values(@unit_id,@product_id);";
                                    cmd.CommandText = sql;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.Parameters.Clear();
                                    cmd.Parameters.Add("@unit_id", MySqlDbType.Int32).Value = ht[i].ToString();
                                    cmd.Parameters.Add("@product_id", MySqlDbType.VarChar).Value = txtId.Text;
                                    cmd.ExecuteNonQuery();
                                }
                            }

                            trans.Commit();
                            conn.Close();
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            conn.Close();
                            MessageBox.Show(ex.ToString());
                            return;
                        }


                        MessageBox.Show("เพิ่มข้อมูลเรียบร้อยแล้ว");
                        ClearFields();
                        SetCheckListBox();
                        MODE = "";
                    }
                    else if ("EDIT".Equals(MODE))
                    {
                        try
                        {
                            //update product
                            string sql = @"UPDATE `product` SET `name` = @name, `product_subcategory_id` = @product_subcategory_id WHERE (`id` = @Original_id);";
                            cmd.CommandText = sql;
                            cmd.CommandType = CommandType.Text;
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@Original_id", MySqlDbType.VarChar).Value = txtId.Text;
                            cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = txtName.Text;
                            cmd.Parameters.Add("@product_subcategory_id", MySqlDbType.Int32).Value = cboProductSubcategory.SelectedValue;

                            cmd.ExecuteNonQuery();

                            //
                            sql = @"delete from `product_unit` where (`product_id` = @product_id);";
                            cmd.CommandText = sql;
                            cmd.CommandType = CommandType.Text;
                            cmd.Parameters.Clear();
                            cmd.Parameters.Add("@product_id", MySqlDbType.VarChar).Value = txtId.Text;
                            cmd.ExecuteNonQuery();


                            //insert unit
                            foreach (string i in chkListBox1.CheckedItems)
                            {
                                if (ht.ContainsKey(i))
                                {
                                    sql = @"insert into product_unit(`unit_id`,`product_id`) values(@unit_id,@product_id);";
                                    cmd.CommandText = sql;
                                    cmd.CommandType = CommandType.Text;
                                    cmd.Parameters.Clear();
                                    cmd.Parameters.Add("@unit_id", MySqlDbType.Int32).Value = ht[i].ToString();
                                    cmd.Parameters.Add("@product_id", MySqlDbType.VarChar).Value = txtId.Text;
                                    cmd.ExecuteNonQuery();
                                }
                            }


                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            conn.Close();
                            MessageBox.Show(ex.ToString());
                            return;
                        }
                        MessageBox.Show("แก้ไขข้อมูลเรียบร้อยแล้ว");
                        panel1.Visible = false;
                        ClearFields();

                        MODE = "";
                    }
                    // Refresh for New Data
                    dataSet1.Tables["product"].Clear();
                    adapter.Fill(dataSet1, "product");

                    EnableOrDisableMainPage(true);

                }
            }
            catch (FormatException ex)
            {
                MessageBox.Show("ใส่ค่าคำนวณผิด กรุณาใส่เป็นตัวเลขเท่านั้น");
            }
        }

        #endregion

        #region Function

        private void EnableOrDisableMainPage(bool isEnable)
        {
            btnAdd.Enabled = isEnable;
            btnEdit.Enabled = isEnable;
            btnDelete.Enabled = isEnable;
            btnClose.Enabled = isEnable;
            dataGridView1.Enabled = isEnable;
        }

        private void ClearFields()
        {
            txtId.Text = "";
            txtName.Text = "";
            cboProductSubcategory.SelectedIndex = 0;
        }

        private void SetContrl()
        {
            //Unit
            string strSQL = " SELECT * FROM  unit";
            adapter2 = new MySqlDataAdapter(strSQL, conn);
            adapter2.Fill(dataSet1, "unit");
            foreach (DataRow dr in dataSet1.Tables["unit"].Rows)
            {
                if (!ht.ContainsKey(dr["name"]))
                {
                    ht.Add(dr["name"], dr["id"]);
                }
            }
            
            //Product Subcategory
            MySqlDataAdapter adapter3 = new MySqlDataAdapter("SELECT * FROM product_subcategory", conn);
            adapter3.Fill(dataSet1, "product_subcategory");
            cboProductSubcategory.DataSource = dataSet1.Tables["product_subcategory"];
            cboProductSubcategory.DisplayMember = "name";
            cboProductSubcategory.ValueMember = "id";
        }

        private void SetCheckListBox()
        {
            chkListBox1.Items.Clear();
            foreach (String htKey in ht.Keys)
            {
                chkListBox1.Items.Add(htKey);
            }
        }

        private void SetCheckListBox(string id)
        {
            DataSet ds = new DataSet();

            string strSQL = " SELECT * FROM  product_unit where product_id = '" + id + "'";
            MySqlDataAdapter adap = new MySqlDataAdapter(strSQL, conn);
            adap.Fill(ds, "product_unit");

            chkListBox1.Items.Clear();
            foreach (String htKey in ht.Keys)
            {
                bool chk = false;
                foreach (DataRow dr in ds.Tables["product_unit"].Rows)
                {
                    if (ht[htKey].Equals(dr["unit_id"]))
                    {
                        chk = true;
                        break;
                    }
                }
                chkListBox1.Items.Add(htKey.ToString(), chk);
            }
        }

        private bool Validation()
        {

            if (txtId.Text.Trim().Length == 0)
            {
                MessageBox.Show("กรุณาเลือกใส่รหัสให้เรียบร้อย");

                txtId.Focus();
                txtId.SelectAll();

                return false;
            }

            if (txtName.Text.Trim().Length == 0)
            {
                MessageBox.Show("กรุณาเลือกใส่ชื่อให้เรียบร้อย");

                txtName.Focus();
                txtName.SelectAll();

                return false;
            }

            if (cboProductSubcategory.SelectedValue == null)
            {
                MessageBox.Show("กรุณาเลือก Subcategory ให้เรียบร้อย");
                return false;
            }

            //=========================================================================================================
            if ("ADD".Equals(MODE)) 
            {
                DataSet ds = new DataSet();
                conn = new MySqlConnection(BangkokSteel.Properties.Settings.Default.bangkoksteelConnectionString);

                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }

                conn.Open();

                adapter = new MySqlDataAdapter("SELECT * FROM product WHERE id = '" + txtId.Text + "'", conn);
                adapter.Fill(ds);

                if (!(ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0))
                {
                    ClearFields();
                    SetCheckListBox();
                    MessageBox.Show("มีรหัสนี้อยู่ในระบบเรียบร้อย");
                    return false;
                }
            }

            //=========================================================================================================

            return true;
        }

        #endregion

        #region Interface

        void MainFormListener.onMainFormClose()
        {
            // TODO
        }

        public void onTabPageAdded()
        {
            // TODO
        }

        #endregion

        private void frmProduct_KeyDown(object sender, KeyEventArgs e)
        {
            switch (e.KeyCode)
            {
                case Keys.Escape:
                    {
                        if (panel1.Visible)
                        {
                            btnCancel_Click(null, null);
                        }
                        else
                        {
                            btnClose_Click(null, null);
                        }
                    }
                    break;
                case Keys.F2:
                    {
                        if (panel1.Visible)
                        {
                            btnOK_Click(null, null);
                        }
                        else
                        {
                            btnAdd_Click(null, null);
                        }
                    }
                    break;

                case Keys.F3:
                    {
                        //if (panel1.Visible)
                        //{
                        //    btnOK_Click(null, null);
                        //}
                        //else
                        //{
                        //    btnEdit_Click(null, null);
                        //}
                        btnEdit_Click(null, null);

                    }
                    break;

                case Keys.F4:
                    {
                        btnDelete_Click(null, null);
                    }
                    break;
            }
        }
    }
}
